package com.howard.modules.base.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.howard.modules.base.paging.Paging;


public class JdbcUtils {

    private final static Logger logger = Logger.getLogger(JdbcUtils.class);

    private JdbcUtils() {
    }

    /**
     * 获取分页SQL
     *
     * @param sql 原始SQL语句
     * @param pageId 页号
     * @param size 一页记录数
     * @return 分页SQL语句
     */
    protected static final String getPagingSQL(String sql, Paging page) {
        if (sql == null || sql.length() == 0) {
            return "";
        }
        if (page == null) {
            return sql;
        }
        StringBuffer sbSQL = new StringBuffer(1024);
        int pageNo = page.getCurrentPage();
        int size = page.getPageSize();
        if (pageNo <= 0) {
            pageNo = 1;
        }
        int maxRow = pageNo * size;
        int minRow = (pageNo - 1) * size;

        sbSQL.append("SELECT * FROM (SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM FROM (");
        sbSQL.append(sql);
        sbSQL.append(")INNER_TABLE) X WHERE X.OUTER_TABLE_ROWNUM <=").append(maxRow);

        if (minRow > 0) {
            sbSQL.insert(0, "SELECT * FROM(");
            sbSQL.append(") OUTER_TABLE WHERE OUTER_TABLE_ROWNUM >").append(minRow);
        }
        return sbSQL.toString();
    }
    
    /**
     * 获取分页SQL
     *
     * @param sql 原始SQL语句
     * @param pageId 页号
     * @param size 一页记录数
     * @return 分页SQL语句
     */
    public static final String getPagingSQL4SqlServer(String sql, Paging page) {
        if (sql == null || sql.length() == 0) {
            return "";
        }
        if (page == null) {
            return sql;
        }
        StringBuffer sbSQL = new StringBuffer(1024);
        int pageNo = page.getCurrentPage();
        int size = page.getPageSize();
        if (pageNo <= 0) {
            pageNo = 1;
        }
        int maxRow = pageNo * size;
        int minRow = (pageNo - 1) * size;
        
        sql = sql.toUpperCase();
        String[] sqlArray = getSplitOrderSQL(sql);
        sbSQL.append("SELECT TOP ").append(maxRow).append(" ROW_NUMBER() OVER (").append(sqlArray[1]).append(") AS ROW_NUM , ");
        sqlArray[0] = sqlArray[0].replaceFirst("SELECT ", sbSQL.toString());
        sbSQL.setLength(0);
        sbSQL.append(sqlArray[0]);
        
        if (minRow > 0) {
            sbSQL.insert(0 , "SELECT * FROM(");
            sbSQL.append(") OUTER_TABLE WHERE ROW_NUM >").append(minRow);
        }
        return sbSQL.toString();
    }

    /**
     * 释放DB资源
     *
     * @param rs ResultSet 结果集
     * @param stat Statement 语句对象
     * @param conn Connection DB联接
     */
    protected static final void closeConnection(ResultSet rs, Statement stat, Connection conn) {
        closeConnection(rs);
        closeConnection(stat);
        closeConnection(conn);
    }

    /**
     * 释放DB资源
     *
     * @param stat Statement 语句对象
     * @param conn Connection DB联接
     */
    protected static final void closeConnection(Statement stat, Connection conn) {
        closeConnection(stat);
        closeConnection(conn);
    }

    /**
     * 释放DB资源
     *
     * @param rs ResultSet 结果集
     */
    protected static final void closeConnection(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.warn("数据库ResultSet关闭异常.", ex);
            }
        }
    }

    /**
     * 释放DB资源
     *
     * @param stat Statement 语句对象
     */
    protected static final void closeConnection(Statement stat) {
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
                logger.warn("数据库Statement关闭异常.", ex);
            }
        }
    }

    /**
     * 释放DB资源
     *
     * @param conn Connection DB联接
     */
    protected static final void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                logger.error("数据库Connection关闭异常.", ex);
            }
        }
    }
    
    /**
     * 返回一个包含2个字符串的数组，第一个字符串为sql不包含排序条件的部分，第二个字符串为sql的排序部分<br>
     * 适用于用到“group by”条件时“order by”语句需特殊处理的情况
     *
     * @return String[]{no order sql, order sql}
     */
    public static String[] getSplitOrderSQL(String sql) {
        if(sql == null){
            return new String[] { "" , "" };
        }
        sql = sql.toUpperCase();
        int iIndex = sql.indexOf(" ORDER ");
        if (iIndex > -1) {
            return new String[] { sql.substring(0, iIndex), sql.substring(iIndex, sql.length()) };
        }
        return new String[] { sql, "" };
    }
}
